--sp_AI_ReminderLetter '7/9/2013','AAI104','Reminder','1/1/2013'
ALTER proc [dbo].[sp_AI_ReminderLetter]
@Date datetime,
@CardCode nvarchar(20),
@Layout nvarchar(20),
@PaymentDate datetime
as
select 
---------------BP Information----------------
T2.CardCode, T2.CardName,T2.U_AI_InvContactCode, 
isnull(T3.Title,'')+' '+ isnull(T3.Name,'')+' ' + isnull(T3.LastName,'') BPContactPerson, 
(select top(1) isnull(Street,'')+' ' + isnull(Block,'')+' ' + isnull(County,'') from CRD1 where AdresType='B' and CardCode=T2.CardCode) BPAddress,
(select top(1) isnull(convert(nvarchar(100),StreetNo),'')+' ' + isnull(convert(nvarchar(100),Building),'') 
	from CRD1 where AdresType='B' and CardCode=T2.CardCode
	and Street=T2.Address /*Default address*/) BPAddress1,  
(select top(1) isnull(State,'') from CRD1 where AdresType='B' and CardCode=T2.CardCode
	and Street=T2.Address /*Default address*/) BPAddress2,
(select top(1) isnull(B.Name,'')+' ' + isnull(t2.ZipCode,'') from CRD1 A join OCRY B on a.Country=B.Code 
	where AdresType='B' and CardCode=T2.CardCode
	and Street=T2.Address /*Default address*/) BPAddress3,
(select top 1 U_AI_DisplayName from [@ALFSETUP] where U_AI_POI=t2.U_AI_PlaceOfIncor) U_PLACEOFINCOR,

--------------Late Fee----------------------
isnull((select top(1) U_AI_lateFees from [@ALFSETUP] 
	where U_AI_POI=T2.U_AI_PLACEOFINCOR 
	and ( (isnull(T2.U_AI_AC,0)<=U_AI_Shares and U_Condition='<') or (isnull(T2.U_AI_AC,0)>U_AI_Shares and U_Condition='>') )
	and T2.U_AI_BVIP1Or2=U_ALF_BVIP1Or2
	and	Convert(date,@Date,101) > Convert(date,U_AI_DateFrom+'-'+convert(nvarchar(4),U_AI_YEARFROM),103)
									and Convert(date,@Date,101) <= Convert(date,U_AI_DateTo+'-'+convert(nvarchar(4),U_AI_YEARTO),103)
						--case when U_AI_POI=1 then YEAR(GETDATE()) else YEAR(GETDATE())+1 end),103)
),0) GovernmentCharge,

---------------Annual License information------------
(select top(1) U_AI_lateFees from [@ALFSETUP] 
	where U_AI_POI=T2.U_AI_PLACEOFINCOR  
		and T2.U_AI_BVIP1Or2=U_ALF_BVIP1Or2 
		and ( (isnull(T2.U_AI_AC,0)<=U_AI_Shares and U_Condition='<') or (isnull(T2.U_AI_AC,0)>U_AI_Shares and U_Condition='>') )
	order by U_AI_lateFees desc) fee2,
	
(select top (1) * from (select top(2) U_AI_lateFees from [@ALFSETUP] 
	where U_AI_POI=T2.U_AI_PLACEOFINCOR  
		and T2.U_AI_BVIP1Or2=U_ALF_BVIP1Or2 
		and ( (isnull(T2.U_AI_AC,0)<=U_AI_Shares and U_Condition='<') or (isnull(T2.U_AI_AC,0)>U_AI_Shares and U_Condition='>') )
	order by U_AI_lateFees asc)U_AI_lateFees  order by U_AI_lateFees desc  ) fee1,
	
(select top(1) Convert(datetime,U_AI_DateTo+'-'+convert(nvarchar(4),U_AI_YEARTO),103) 
	from [@ALFSETUP] 
	where U_AI_POI=T2.U_AI_PLACEOFINCOR  
		and ( (isnull(T2.U_AI_AC,0)<=U_AI_Shares and U_Condition='<') or (isnull(T2.U_AI_AC,0)>U_AI_Shares and U_Condition='>') )
		and T2.U_AI_BVIP1Or2=U_ALF_BVIP1Or2
	order by U_AI_lateFees asc) from1, 
		
(select top(1) (Convert(datetime,U_AI_DateTo+'-'+convert(nvarchar(4),U_AI_YEARTO),103)) U_AI_DateTo
	from [@ALFSETUP] 
	where U_AI_lateFees=
	(select top (1) * from (select top(2) U_AI_lateFees from [@ALFSETUP] 
	where U_AI_POI=T2.U_AI_PLACEOFINCOR  
		and T2.U_AI_BVIP1Or2=U_ALF_BVIP1Or2 
		and ( (isnull(T2.U_AI_AC,0)<=U_AI_Shares and U_Condition='<') or (isnull(T2.U_AI_AC,0)>U_AI_Shares and U_Condition='>') )
	order by U_AI_lateFees asc)U_AI_lateFees  order by U_AI_lateFees desc  ) 
	and  U_AI_POI=T2.U_AI_PLACEOFINCOR  
	and T2.U_AI_BVIP1Or2=U_ALF_BVIP1Or2 
	and ( (isnull(T2.U_AI_AC,0)<=U_AI_Shares and U_Condition='<') or (isnull(T2.U_AI_AC,0)>U_AI_Shares and U_Condition='>') )
	--  and ( (isnull(T2.U_AI_AC,0)<=U_AI_Shares and U_Condition='<')) --or (isnull(T2.U_AI_AC,0)>U_AI_Shares and U_Condition='>') )
	
	)  
	
	from2,
	
	
(select top(1) Convert(datetime,U_AI_DateTo+'-'+convert(nvarchar(4),U_AI_YEARTO),103) 
	from [@ALFSETUP] 
	where U_AI_POI=T2.U_AI_PLACEOFINCOR  
		and ( (isnull(T2.U_AI_AC,0)<=U_AI_Shares and U_Condition='<') or (isnull(T2.U_AI_AC,0)>U_AI_Shares and U_Condition='>') )
		and T2.U_AI_BVIP1Or2=U_ALF_BVIP1Or2
	order by U_AI_lateFees desc)

 from3, 
 (select top(1) convert(datetime,dateadd(dd,-(DAY(DATEADD(mm,1,
 --Convert(datetime,
 --(U_AI_DateTo+'-'+convert(nvarchar(4),YEAR(GETDATE())) ),101) 
(select top(1) Convert(datetime,U_AI_DateTo+'-'+convert(nvarchar(4),U_AI_YEARTO),103) 
	from [@ALFSETUP] 
	where U_AI_POI=T2.U_AI_PLACEOFINCOR  
		and ( (isnull(T2.U_AI_AC,0)<=U_AI_Shares and U_Condition='<') or (isnull(T2.U_AI_AC,0)>U_AI_Shares and U_Condition='>') )
		and T2.U_AI_BVIP1Or2=U_ALF_BVIP1Or2
	order by U_AI_lateFees desc) 
 
 
 ))-1),DateAdd(mm,1,
 --Convert(datetime,(U_AI_DateTo+'-'+convert(nvarchar(4),YEAR(GETDATE())) ),101) 
 
 (select top(1) Convert(datetime,U_AI_DateTo+'-'+convert(nvarchar(4),U_AI_YEARTO),103) 
	from [@ALFSETUP] 
	where U_AI_POI=T2.U_AI_PLACEOFINCOR  
		and ( (isnull(T2.U_AI_AC,0)<=U_AI_Shares and U_Condition='<') or (isnull(T2.U_AI_AC,0)>U_AI_Shares and U_Condition='>') )
		and T2.U_AI_BVIP1Or2=U_ALF_BVIP1Or2
	order by U_AI_lateFees desc)
 )),101)
 
 --
 
-- (select year(getdate()))
-- (select top(1) Convert(datetime,(U_AI_DateTo+'-'+convert(nvarchar(4),YEAR(GETDATE())) ),101) 
	--from [@ALFSETUP] 
	--where U_AI_POI=T2.U_AI_PLACEOFINCOR  
	--	and ( (isnull(T2.U_AI_AC,0)<=U_AI_Shares and U_Condition='<') or (isnull(T2.U_AI_AC,0)>U_AI_Shares and U_Condition='>') )
	--	and T2.U_AI_BVIP1Or2=U_ALF_BVIP1Or2
	--order by U_AI_lateFees desc)
	from [@ALFSETUP] 
	where U_AI_POI=T2.U_AI_PLACEOFINCOR  
		and ( (isnull(T2.U_AI_AC,0)<=U_AI_Shares and U_Condition='<') or (isnull(T2.U_AI_AC,0)>U_AI_Shares and U_Condition='>') )
		and T2.U_AI_BVIP1Or2=U_ALF_BVIP1Or2
	order by U_AI_lateFees desc)

--(SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,GETDATE()))-1),DATEADD(mm,1,GETDATE())),101))		

		 from4,
		
	
----------------OPEN INVOICE------------------
case when T1.TransType in ('24','46') then T1.TransId else T1.BaseRef end DocEntry, T1.Ref2,
T1.DueDate,T1.RefDate, DATEDIFF(dd,T1.DueDate,@Date)OverDueDays,T6.U_AI_Type,
case
WHEN T1.TransType = '-2' THEN 'OB'
WHEN T1.TransType = '13' THEN 'IN' 
WHEN T1.TransType = '14' THEN 'CN'
WHEN T1.TransType = '15' THEN 'DN' 
WHEN T1.TransType = '16' THEN 'RE'
WHEN T1.TransType = '18' THEN 'PU'
WHEN T1.TransType = '19' THEN 'PC' 
WHEN T1.TransType = '20' THEN 'PD' 
WHEN T1.TransType = '202' THEN 'PW' 
WHEN T1.TransType = '21' THEN 'PR' 
WHEN T1.TransType = '24' THEN 'RC' 
WHEN T1.TransType = '30' THEN 'JE'
WHEN T1.TransType = '46' THEN 'PS' 
WHEN T1.TransType = '58' THEN 'ST' 
WHEN T1.TransType = '59' THEN 'SI'
WHEN T1.TransType = '60' THEN 'SO' 
WHEN T1.TransType = '67' THEN 'IM'
WHEN T1.TransType = '69' THEN 'IF' 
WHEN T1.TransType = '162' THEN 'MR'
WHEN T1.TransType = '203' THEN 'DT'
else '' end TransType,
Debit-(case when isCredit='D' then isnull(ReconSum,0) else 0 end)-(Credit-(case when isCredit='C' then isnull(ReconSum,0) else 0 end)) BalanceDue ,
------------------PIC-------------------------
isnull(T5.U_AI_PICTitle,'') +' '+ isnull(T5.U_AI_PICName,'') PICName, isnull(T5.Name,'') PICPhone


from JDT1 T1 with (nolock)
Inner join OINV T6 with (nolock) on T1.BaseRef = T6.DocNum 
join OCRD T2 with (nolock) on T1.ShortName=T2.CardCode 
left join OCPR T3 with(nolock) on t3.CardCode=T2.CardCode and T2.CntctPrsn=T3.Name
left join
(  
	select t1.TransID, t1.TransRowID, sum(isnull(t1.ReconSum,0)) ReconSum, t1.isCredit  
	from ITR1 t1 with (nolock) join OITR t2 with (nolock) on T1.ReconNum=T2.ReconNum  
	where t2.ReconDate<=@Date   
	group by t1.TransID, T1.TransRowID, T1.isCredit   
) T4 on T1.TransID=T4.TransID and T1.Line_ID=T4.TransRowID 
left join [@PIC] T5 on T5.Code=T2.U_AI_Administrator
where T4.TransId is null and T1.RefDate<=@Date
and T2.CardCode=@CardCode
order by DocEntry

GO


